{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "017a0ac3-cc4f-46bf-bf33-7983ab6b00ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "import math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "266faaef-f8de-4474-a322-1ef811a9dad5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/b2/q4rjvqh51qq7x76jh6_m05k40000gn/T/ipykernel_76722/899425151.py:11: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql(query, conn)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>stock_name</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-03-01</td>\n",
       "      <td>宇通客车</td>\n",
       "      <td>470846806.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-03-04</td>\n",
       "      <td>宇通客车</td>\n",
       "      <td>373105452.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-03-05</td>\n",
       "      <td>宇通客车</td>\n",
       "      <td>571896970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-03-06</td>\n",
       "      <td>宇通客车</td>\n",
       "      <td>312897819.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-03-07</td>\n",
       "      <td>宇通客车</td>\n",
       "      <td>256013983.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt stock_name       amount\n",
       "0  2024-03-01       宇通客车  470846806.0\n",
       "1  2024-03-04       宇通客车  373105452.0\n",
       "2  2024-03-05       宇通客车  571896970.0\n",
       "3  2024-03-06       宇通客车  312897819.0\n",
       "4  2024-03-07       宇通客车  256013983.0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DB_CONFIG = {\n",
    "    'user': 'komi',\n",
    "    'password': 'komi2025',\n",
    "    'database': 'komi',\n",
    "    'host': 'localhost',\n",
    "    'port': 3306\n",
    "}\n",
    "\n",
    "with pymysql.connect(**DB_CONFIG) as conn:\n",
    "    query = \"SELECT dt, stock_name, amount FROM st_cn_stock_trade_inc_d WHERE dt BETWEEN '2024-01-01' AND '2024-05-17'\"\n",
    "    df = pd.read_sql(query, conn)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ac6718a1-63f6-4d40-9dbe-4d39ba79c164",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def draw_chart(df, col):\n",
    "    df.set_index('dt', inplace=True)\n",
    "    df[col].plot(kind='bar')\n",
    "    plt.show()\n",
    "\n",
    "\n",
    "def find_window(df, dt, stock_name, T, p1, p2):\n",
    "    \"\"\"\n",
    "    df 交易数据\n",
    "    dt 时间窗口右侧日期（对应end_dt，上定时调度时用最新日期）\n",
    "    T  时间窗口宽度（取近T个交易日）\n",
    "    p1 左侧子窗口的p1分位数\n",
    "    p2 右侧子窗口的p2分位数\n",
    "\n",
    "    如果 p2 > p1 则表示这个区间符合要求\n",
    "    \"\"\"\n",
    "    date_fmt = '%Y-%m-%d'\n",
    "    end_dt = datetime.date(datetime.strptime(dt, date_fmt))\n",
    "\n",
    "    # T 必须为奇数\n",
    "    if int(T/2) == T/2:\n",
    "        T -= 1\n",
    "\n",
    "    # 限定范围\n",
    "    stock_df = df[df['stock_name'] == stock_name]\n",
    "    \n",
    "    # 根据交易日倒序，取第T个交易日\n",
    "    days = stock_df.sort_values(by='dt', ascending=False)['dt'].head(T+1)\n",
    "    start_dt = days.iloc[-1]\n",
    "    first_mid_dt = days.iloc[math.ceil(T/2)]\n",
    "    second_mid_dt = days.iloc[math.floor(T/2)]\n",
    "    \n",
    "    # 获得时间窗口\n",
    "    w1 = stock_df[(stock_df['dt'] >= start_dt) & (stock_df['dt'] <= first_mid_dt)]\n",
    "    w2 = stock_df[(stock_df['dt'] >= second_mid_dt) & (stock_df['dt'] <= end_dt)]\n",
    "\n",
    "    # 作图\n",
    "    # draw_chart(pd.concat([w1, w2]), 'amount')\n",
    "\n",
    "    return w1['amount'].quantile(p1) <  w2['amount'].quantile(p2)\n",
    "\n",
    "\n",
    "find_window(df, '2024-05-17', '招商银行', 60, 0.9, 0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eeecd61e-39b1-4e47-a4f2-2f5d66607193",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d6be79b-960e-4593-8061-ababa699e604",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
